﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace welwitschia.Relatorios
{
    public partial class RequisicaoInternaControler : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

            String nrequisicao = Request.QueryString["nrid"];
            ReportDocument rptDoc = new ReportDocument();
            
            RequisicaoInternaModel ds = new RequisicaoInternaModel(); // .xsd file name
            DataTable dt = new DataTable();
            // Just set the name of data table
            dt.TableName = "Crystal Report Example";
            dt = ObterDados(nrequisicao); //This function is located below this function
            ds.Tables[0].Merge(dt);
            
            TotalRefeicoesModel rptTotalRefeicoes = new TotalRefeicoesModel();
            dt = new DataTable();
            // Just set the name of data table
            dt.TableName = "TotalRefeicoesTable";
            dt = ObterDadosTotalRefeicoes(nrequisicao); //This function is located below this function
            rptTotalRefeicoes.Tables[0].Merge(dt);


            TotalGruposProdutosModel rptTotalGruposProdutos = new TotalGruposProdutosModel();
            dt = new DataTable();
            // Just set the name of data table
            dt.TableName = "TotalGruposProdutosTable";
            dt = ObterDadosGruposProdutosRefeicoes(nrequisicao); //This function is located below this function
            rptTotalGruposProdutos.Tables[0].Merge(dt);

            // Your .rpt file path will be below
            rptDoc.Load(Server.MapPath("RequisicaoInternaReport.rpt")); 
            //set dataset to the report viewer.
            rptDoc.SetDataSource(ds);

            rptDoc.Subreports["subTotalRefeicoesReport.rpt"].SetDataSource(rptTotalRefeicoes);
            rptDoc.Subreports["subTotalGruposProdutosRefeicaoReport.rpt"].SetDataSource(rptTotalGruposProdutos);
            CrystalReportViewer1.ReportSource = rptDoc;
        }

        public DataTable ObterDados(String numeroRequisicao)
        {
            //Connection string replace 'databaseservername' with your db server name
            string sqlCon = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            SqlConnection Con = new SqlConnection(sqlCon);
            SqlCommand cmd = new SqlCommand();
            DataSet ds = null;
            SqlDataAdapter adapter;
            try
            {
                Con.Open();
                //Chamada à SP.
                cmd.CommandText = "proc_rptRequisicaoInternaAllProdutos_O1";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@NumeroRequisicao", Convert.ToInt64(numeroRequisicao)));
                cmd.Connection = Con;
                ds = new DataSet();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "RequisicaoInternaTable");


            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
                if (Con.State != ConnectionState.Closed)
                    Con.Close();
            }
            return ds.Tables[0];

        }

        public DataTable ObterDadosTotalRefeicoes(String numeroRequisicao)
        {
            //Connection string replace 'databaseservername' with your db server name
            string sqlCon = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            SqlConnection Con = new SqlConnection(sqlCon);
            SqlCommand cmd = new SqlCommand();
            DataSet ds = null;
            SqlDataAdapter adapter;
            try
            {
                Con.Open();
                //Chamada à SP.
                cmd.CommandText = "proc_rptTotalRefeicaoRequisicao_O1";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@NumeroRequisicao", Convert.ToInt64(numeroRequisicao)));
                cmd.Connection = Con;
                ds = new DataSet();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "TotalRefeicoesTable");

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
                if (Con.State != ConnectionState.Closed)
                    Con.Close();
            }
            return ds.Tables[0];

        }


        public DataTable ObterDadosGruposProdutosRefeicoes(String numeroRequisicao)
        {
            //Connection string replace 'databaseservername' with your db server name
            string sqlCon = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            SqlConnection Con = new SqlConnection(sqlCon);
            SqlCommand cmd = new SqlCommand();
            DataSet ds = null;
            SqlDataAdapter adapter;
            try
            {
                Con.Open();
                //Chamada à SP.
                cmd.CommandText = "proc_rptTotalGruposProdutosRequisicao_O1";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new SqlParameter("@NumeroRequisicao", Convert.ToInt64(numeroRequisicao)));
                cmd.Connection = Con;
                ds = new DataSet();
                adapter = new SqlDataAdapter(cmd);
                adapter.Fill(ds, "TotalGruposProdutosTable");

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Dispose();
                if (Con.State != ConnectionState.Closed)
                    Con.Close();
            }
            return ds.Tables[0];

        }
    }
}